﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using System.Text.RegularExpressions;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace BoletoElectronicoDesktop
{


    public static class RolDAO
    {

        //obtiene la lista de funcionalidades existentes
        public static DataTable ObtenerFuncionalidades()
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Funciones_Filtro";
            command.CommandTimeout = 10;
           SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }

        //determina la cant de roles con el mismo nombre que el ingresado
        public static int ExisteRol(string rol)
        {
           
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Cantidad";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@nombreRol", rol);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        //agrega un nuevo rol a la tabla de roles
        public static void AgregarRol(string rol)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Alta";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@rol", rol);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }

        //devuelve el id de un rol
        public static int ObtenerId(string rol)
        {
    
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Id";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@nombreRol", rol);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        //agrega una nueva funcionalidad para un  rol a la tabla de FunXRol
        public static void InsertarFuncionalidadPorRol(string rol, string funcionalidad)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Funcionalidad_Rol_Alta";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@rol", rol);
            command.Parameters.AddWithValue("@func", funcionalidad);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();

        }
        //los roles habilitados con nombre similar al ingresado
        public static DataTable FiltrarRolesHabilitados(string rol)
        {
   
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Filtro";
            command.CommandTimeout = 10;
            //agrego el parámetro si no es null
            if (string.Compare(rol,"")!=0)
                command.Parameters.AddWithValue("@rol", rol);
            command.Parameters.AddWithValue("@habilitado", "True");
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }


        //inhabilita un rol y lo elimina de los usuarios que lo poseen
        public static void InhabilitarRol(string idRol)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Inhabilitar";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@id", idRol);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }

        //los roles  con nombre similar al ingresado habilitados y no habilitados
        public static DataTable FiltrarRoles(string rol)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Filtro";
            command.CommandTimeout = 10;
            if (string.Compare(rol, "") != 0)
                command.Parameters.AddWithValue("@rol", rol);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();


            return dataTable;

        }
        //obtiene la lista de funcionalidades de un rol
        public static int ObtenerFuncionalidadDelRol(string idRol, string idFuncionalidad)
        {
          
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Funcionalidad_delRol";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@idRol", idRol);
            command.Parameters.AddWithValue("@idFunc", idFuncionalidad);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }

        //actualiza nombre de rol y/o habilitacion del rol
        public static void ActualizarRol(string idRol, string nombreRol, string habilitado)
        {
        
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Actualizar";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@id", idRol);
            command.Parameters.AddWithValue("@nombre", nombreRol);
            command.Parameters.AddWithValue("@habilitado", habilitado);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }

        //elimina las relaciones del rol con sus funcionalidades
        public static void EliminarFuncionalidadesDeRol(string idRol)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_EliminarFuncionalidades";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@idRol", idRol);
  
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }
        }


}
